﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using rock.UI.common;

namespace rock.DAL
{
	/// <summary>
	/// 数据访问类:DalRENT_CONTRACT
	/// </summary>
	public partial class DalRENT_CONTRACT
	{
		public DalRENT_CONTRACT()
		{}
		#region  Method

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("SEQID", "RENT_CONTRACT"); 
		}

        public int GetMaxIdByContractNumber()
        {
            String date = DateTime.Now.ToString("yyyyMMdd");
            String sql = "SELECT COUNT(1) FROM RENT_CONTRACT WHERE CONTRACT_NUMBER LIKE 'D" + date + "-N____'";
            SqlParameter[] parameters = { };
            object obj = DbHelperSQL.GetSingle(sql, parameters);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            return cmdresult;
        }


		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int SEQID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from RENT_CONTRACT");
			strSql.Append(" where SEQID=@SEQID");
			SqlParameter[] parameters = {
					new SqlParameter("@SEQID", SqlDbType.Int,4)
			};
			parameters[0].Value = SEQID;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
        public SqlParamInfo Add(rock.Model.ModelRENT_CONTRACT model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into RENT_CONTRACT(");
			strSql.Append("CONTRACT_NUMBER,RENT_CUSTOM_SEQID,HOUSE_ROOM_SEQID,START_DATE,MONTH_COUNT,STOP_DATE,RENT_MONEY,RENT_TYPE,FILE_PATH,STATUS,CONTRACT_ALL_MONEY,NOTE)");
			strSql.Append(" values (");
			strSql.Append("@CONTRACT_NUMBER,@RENT_CUSTOM_SEQID,@HOUSE_ROOM_SEQID,@START_DATE,@MONTH_COUNT,@STOP_DATE,@RENT_MONEY,@RENT_TYPE,@FILE_PATH,@STATUS,@CONTRACT_ALL_MONEY,@NOTE)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@CONTRACT_NUMBER", SqlDbType.VarChar,30),
					new SqlParameter("@RENT_CUSTOM_SEQID", SqlDbType.Int,4),
					new SqlParameter("@HOUSE_ROOM_SEQID", SqlDbType.Int,4),
					new SqlParameter("@START_DATE", SqlDbType.DateTime),
					new SqlParameter("@MONTH_COUNT", SqlDbType.Int,4),
					new SqlParameter("@STOP_DATE", SqlDbType.DateTime),
					new SqlParameter("@RENT_MONEY", SqlDbType.Decimal,9),
					new SqlParameter("@RENT_TYPE", SqlDbType.Int,4),
					new SqlParameter("@FILE_PATH", SqlDbType.VarChar,200),
					new SqlParameter("@STATUS", SqlDbType.Int,4),
					new SqlParameter("@CONTRACT_ALL_MONEY", SqlDbType.Decimal,9),
					new SqlParameter("@NOTE", SqlDbType.VarChar,300)};
			parameters[0].Value = model.CONTRACT_NUMBER;
			parameters[1].Value = model.RENT_CUSTOM_SEQID;
			parameters[2].Value = model.HOUSE_ROOM_SEQID;
			parameters[3].Value = model.START_DATE;
			parameters[4].Value = model.MONTH_COUNT;
			parameters[5].Value = model.STOP_DATE;
			parameters[6].Value = model.RENT_MONEY;
			parameters[7].Value = model.RENT_TYPE;
			parameters[8].Value = model.FILE_PATH;
			parameters[9].Value = model.STATUS;
			parameters[10].Value = model.CONTRACT_ALL_MONEY;
			parameters[11].Value = model.NOTE;

            return new SqlParamInfo() { sql = strSql.ToString(), parameters = parameters };
            //object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
            //if (obj == null)
            //{
            //    return 0;
            //}
            //else
            //{
            //    return Convert.ToInt32(obj);
            //}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
        public SqlParamInfo Update(rock.Model.ModelRENT_CONTRACT model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update RENT_CONTRACT set ");
			strSql.Append("CONTRACT_NUMBER=@CONTRACT_NUMBER,");
			strSql.Append("RENT_CUSTOM_SEQID=@RENT_CUSTOM_SEQID,");
			strSql.Append("HOUSE_ROOM_SEQID=@HOUSE_ROOM_SEQID,");
			strSql.Append("START_DATE=@START_DATE,");
			strSql.Append("MONTH_COUNT=@MONTH_COUNT,");
			strSql.Append("STOP_DATE=@STOP_DATE,");
			strSql.Append("RENT_MONEY=@RENT_MONEY,");
			strSql.Append("RENT_TYPE=@RENT_TYPE,");
			strSql.Append("FILE_PATH=@FILE_PATH,");
			strSql.Append("STATUS=@STATUS,");
			strSql.Append("CONTRACT_ALL_MONEY=@CONTRACT_ALL_MONEY,");
			strSql.Append("NOTE=@NOTE");
			strSql.Append(" where SEQID=@SEQID");
			SqlParameter[] parameters = {
					new SqlParameter("@CONTRACT_NUMBER", SqlDbType.VarChar,30),
					new SqlParameter("@RENT_CUSTOM_SEQID", SqlDbType.Int,4),
					new SqlParameter("@HOUSE_ROOM_SEQID", SqlDbType.Int,4),
					new SqlParameter("@START_DATE", SqlDbType.DateTime),
					new SqlParameter("@MONTH_COUNT", SqlDbType.Int,4),
					new SqlParameter("@STOP_DATE", SqlDbType.DateTime),
					new SqlParameter("@RENT_MONEY", SqlDbType.Decimal,9),
					new SqlParameter("@RENT_TYPE", SqlDbType.Int,4),
					new SqlParameter("@FILE_PATH", SqlDbType.VarChar,200),
					new SqlParameter("@STATUS", SqlDbType.Int,4),
					new SqlParameter("@CONTRACT_ALL_MONEY", SqlDbType.Decimal,9),
					new SqlParameter("@NOTE", SqlDbType.VarChar,300),
					new SqlParameter("@SEQID", SqlDbType.Int,4)};
			parameters[0].Value = model.CONTRACT_NUMBER;
			parameters[1].Value = model.RENT_CUSTOM_SEQID;
			parameters[2].Value = model.HOUSE_ROOM_SEQID;
			parameters[3].Value = model.START_DATE;
			parameters[4].Value = model.MONTH_COUNT;
			parameters[5].Value = model.STOP_DATE;
			parameters[6].Value = model.RENT_MONEY;
			parameters[7].Value = model.RENT_TYPE;
			parameters[8].Value = model.FILE_PATH;
			parameters[9].Value = model.STATUS;
			parameters[10].Value = model.CONTRACT_ALL_MONEY;
			parameters[11].Value = model.NOTE;
			parameters[12].Value = model.SEQID;

            return new SqlParamInfo() { sql = strSql.ToString(), parameters = parameters };
            //int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
            //if (rows > 0)
            //{
            //    return true;
            //}
            //else
            //{
            //    return false;
            //}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
        public SqlParamInfo Delete(int SEQID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from RENT_CONTRACT ");
			strSql.Append(" where SEQID=@SEQID");
			SqlParameter[] parameters = {
					new SqlParameter("@SEQID", SqlDbType.Int,4)
			};
			parameters[0].Value = SEQID;
            return new SqlParamInfo() { sql = strSql.ToString(), parameters = parameters };
            //int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
            //if (rows > 0)
            //{
            //    return true;
            //}
            //else
            //{
            //    return false;
            //}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string SEQIDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from RENT_CONTRACT ");
			strSql.Append(" where SEQID in ("+SEQIDlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public rock.Model.ModelRENT_CONTRACT GetModel(int SEQID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 SEQID,CONTRACT_NUMBER,RENT_CUSTOM_SEQID,HOUSE_ROOM_SEQID,START_DATE,MONTH_COUNT,STOP_DATE,RENT_MONEY,RENT_TYPE,FILE_PATH,STATUS,CONTRACT_ALL_MONEY,NOTE from RENT_CONTRACT ");
			strSql.Append(" where SEQID=@SEQID");
			SqlParameter[] parameters = {
					new SqlParameter("@SEQID", SqlDbType.Int,4)
			};
			parameters[0].Value = SEQID;

			rock.Model.ModelRENT_CONTRACT model=new rock.Model.ModelRENT_CONTRACT();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["SEQID"]!=null && ds.Tables[0].Rows[0]["SEQID"].ToString()!="")
				{
					model.SEQID=int.Parse(ds.Tables[0].Rows[0]["SEQID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["CONTRACT_NUMBER"]!=null && ds.Tables[0].Rows[0]["CONTRACT_NUMBER"].ToString()!="")
				{
					model.CONTRACT_NUMBER=ds.Tables[0].Rows[0]["CONTRACT_NUMBER"].ToString();
				}
				if(ds.Tables[0].Rows[0]["RENT_CUSTOM_SEQID"]!=null && ds.Tables[0].Rows[0]["RENT_CUSTOM_SEQID"].ToString()!="")
				{
					model.RENT_CUSTOM_SEQID=int.Parse(ds.Tables[0].Rows[0]["RENT_CUSTOM_SEQID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["HOUSE_ROOM_SEQID"]!=null && ds.Tables[0].Rows[0]["HOUSE_ROOM_SEQID"].ToString()!="")
				{
					model.HOUSE_ROOM_SEQID=int.Parse(ds.Tables[0].Rows[0]["HOUSE_ROOM_SEQID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["START_DATE"]!=null && ds.Tables[0].Rows[0]["START_DATE"].ToString()!="")
				{
					model.START_DATE=DateTime.Parse(ds.Tables[0].Rows[0]["START_DATE"].ToString());
				}
				if(ds.Tables[0].Rows[0]["MONTH_COUNT"]!=null && ds.Tables[0].Rows[0]["MONTH_COUNT"].ToString()!="")
				{
					model.MONTH_COUNT=int.Parse(ds.Tables[0].Rows[0]["MONTH_COUNT"].ToString());
				}
				if(ds.Tables[0].Rows[0]["STOP_DATE"]!=null && ds.Tables[0].Rows[0]["STOP_DATE"].ToString()!="")
				{
					model.STOP_DATE=DateTime.Parse(ds.Tables[0].Rows[0]["STOP_DATE"].ToString());
				}
				if(ds.Tables[0].Rows[0]["RENT_MONEY"]!=null && ds.Tables[0].Rows[0]["RENT_MONEY"].ToString()!="")
				{
					model.RENT_MONEY=decimal.Parse(ds.Tables[0].Rows[0]["RENT_MONEY"].ToString());
				}
				if(ds.Tables[0].Rows[0]["RENT_TYPE"]!=null && ds.Tables[0].Rows[0]["RENT_TYPE"].ToString()!="")
				{
					model.RENT_TYPE=int.Parse(ds.Tables[0].Rows[0]["RENT_TYPE"].ToString());
				}
				if(ds.Tables[0].Rows[0]["FILE_PATH"]!=null && ds.Tables[0].Rows[0]["FILE_PATH"].ToString()!="")
				{
					model.FILE_PATH=ds.Tables[0].Rows[0]["FILE_PATH"].ToString();
				}
				if(ds.Tables[0].Rows[0]["STATUS"]!=null && ds.Tables[0].Rows[0]["STATUS"].ToString()!="")
				{
					model.STATUS=int.Parse(ds.Tables[0].Rows[0]["STATUS"].ToString());
				}
				if(ds.Tables[0].Rows[0]["CONTRACT_ALL_MONEY"]!=null && ds.Tables[0].Rows[0]["CONTRACT_ALL_MONEY"].ToString()!="")
				{
					model.CONTRACT_ALL_MONEY=decimal.Parse(ds.Tables[0].Rows[0]["CONTRACT_ALL_MONEY"].ToString());
				}
				if(ds.Tables[0].Rows[0]["NOTE"]!=null && ds.Tables[0].Rows[0]["NOTE"].ToString()!="")
				{
					model.NOTE=ds.Tables[0].Rows[0]["NOTE"].ToString();
				}
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select SEQID,CONTRACT_NUMBER,RENT_CUSTOM_SEQID,HOUSE_ROOM_SEQID,START_DATE,MONTH_COUNT,STOP_DATE,RENT_MONEY,RENT_TYPE,FILE_PATH,STATUS,CONTRACT_ALL_MONEY,NOTE ");
			strSql.Append(" FROM RENT_CONTRACT ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" SEQID,CONTRACT_NUMBER,RENT_CUSTOM_SEQID,HOUSE_ROOM_SEQID,START_DATE,MONTH_COUNT,STOP_DATE,RENT_MONEY,RENT_TYPE,FILE_PATH,STATUS,CONTRACT_ALL_MONEY,NOTE ");
			strSql.Append(" FROM RENT_CONTRACT ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		 
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
        public PageDataSet GetListByPage(String where, string orderby, int pageNo, int pageSize)
		{
            String sql = @"SELECT 
                        RENT_CONTRACT.*  ,
                        RENT_CUSTOM.NAME AS CUSTOM_NAME , 
                        TEMP.XIAOQU_NAME , 
                        TEMP.DONG_NAME ,
                        HOUSE_ROOM.HOUSE_NUMBER AS ROOM_NAME 
                        FROM RENT_CONTRACT 
                        LEFT JOIN HOUSE_ROOM  ON RENT_CONTRACT.HOUSE_ROOM_SEQID =  HOUSE_ROOM.SEQID
                        LEFT JOIN RENT_CUSTOM ON  RENT_CONTRACT.RENT_CUSTOM_SEQID = RENT_CUSTOM.SEQID
                        LEFT JOIN
                        (
                        SELECT HOUSE_XIAOQU.NAME as XIAOQU_NAME , HOUSE_DONG.NAME AS DONG_NAME, ALL_FLOOR_START,ALL_FLOOR_END , HAS_DIANTI ,
                        HOUSE_XIAOQU.SEQID  as XIAOQU_SEQID , HOUSE_DONG.SEQID  as DONG_SEQID 
                        FROM HOUSE_XIAOQU , HOUSE_DONG WHERE HOUSE_XIAOQU.SEQID = HOUSE_DONG.HOUSE_XIAOQU_SEQID
                        )  AS TEMP 
                        ON  HOUSE_ROOM.HOUSE_DONG_SEQID = TEMP.DONG_SEQID
                        ";
            if (where != null && !where.Equals(""))
            {
                sql += " WHERE " + where;
            }

            PageDataSet pds = new PageDataSet();
            pds.AllRecord = DbHelperSQL.GetRecordCount(sql);
            pds.DataSet = DbHelperSQL.GetListByPage(sql, orderby, pageNo, pageSize);

            return pds;

        }



        public DataSet getRENT_CONTRACT_DETAILINFO(int SEQID) {
            String sql = @"SELECT 
                        RENT_CONTRACT.CONTRACT_NUMBER  ,
                        RENT_CONTRACT.RENT_MONEY  ,
                        RENT_CONTRACT.STOP_DATE  ,
                        RENT_CONTRACT.START_DATE  ,
                        RENT_CUSTOM.NAME AS CUSTOM_NAME , 
                        TEMP.XIAOQU_NAME , 
                        TEMP.DONG_NAME ,
                        HOUSE_ROOM.HOUSE_NUMBER AS ROOM_NAME,  
                        HOUSE_ROOM.ROOM_SIZE AS ROOM_SIZE
                        FROM RENT_CONTRACT 
                        LEFT JOIN HOUSE_ROOM  ON RENT_CONTRACT.HOUSE_ROOM_SEQID =  HOUSE_ROOM.SEQID
                        LEFT JOIN RENT_CUSTOM ON  RENT_CONTRACT.RENT_CUSTOM_SEQID = RENT_CUSTOM.SEQID
                        LEFT JOIN
                        (
                        SELECT HOUSE_XIAOQU.NAME as XIAOQU_NAME , HOUSE_DONG.NAME AS DONG_NAME, ALL_FLOOR_START,ALL_FLOOR_END , HAS_DIANTI ,
                        HOUSE_XIAOQU.SEQID  as XIAOQU_SEQID , HOUSE_DONG.SEQID  as DONG_SEQID 
                        FROM HOUSE_XIAOQU , HOUSE_DONG WHERE HOUSE_XIAOQU.SEQID = HOUSE_DONG.HOUSE_XIAOQU_SEQID
                        )  AS TEMP 
                        ON  HOUSE_ROOM.HOUSE_DONG_SEQID = TEMP.DONG_SEQID
                        WHERE RENT_CONTRACT.SEQID = " + SEQID;
            return DbHelperSQL.Query(sql);

             
        }


        public DataSet GetContractNotifyMessage(int NotifyDay)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@NotifyDay", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NotifyDay;
            return DbHelperSQL.RunProcedure("GetContractNotifyMessage", parameters, "ds");
        }









		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "RENT_CONTRACT";
			parameters[1].Value = "SEQID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  Method
	}
}

